package web.demo.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import web.demo.dao.IFoodDao;
import web.demo.entity.Food;
import web.demo.entity.FoodTable;
import web.demo.utils.Condition;
import web.demo.utils.JdbcUtils;
import web.demo.utils.PageBean;

public class FoodDao extends BaseDao implements IFoodDao{

	@Override
	public boolean addFood(Food food) {
		String sql="INSERT INTO food (NAME,price,vip_price,foodType_id,description,img) VALUES(?,?,?,?,?,?)";
		Object[] params={
			food.getName(),
			food.getPrice(),
			food.getVip_price(),
			food.getFoodType_id(),
			food.getDescription(),
			food.getImg()
		};
		try {
			int result=JdbcUtils.getQueryRunner().update(sql,params);
			if(result>0){
				return true;
			}else{
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void update(Food food) {
		StringBuilder sb=new StringBuilder();
		sb.append(" update food set ");
		sb.append("  name=?,");
		sb.append("  price=?,");
		sb.append("  vip_price=?,");
		sb.append("  foodType_id=?,");
		sb.append("  description=?,");
		sb.append("  img=? ");
		sb.append(" where ");
		sb.append("  id=?");
		Object[] params={
				food.getName(),
				food.getPrice(),
				food.getVip_price(),
				food.getFoodType_id(),
				food.getDescription(),
				food.getImg(),
				food.getId()
			};
		try{
			JdbcUtils.getQueryRunner().update(sb.toString(),params);
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	@Override
	public boolean delete(int id) {
		String sql="delete from food where id=? ";
		conn=JdbcUtils.getConnection();
		int result=update(conn,sql,id);
		if(result>0){
			return true;
		}
		return false;
	}

	@Override
	public List<Food> getFoodAll(PageBean pageBean) {
		StringBuilder sb=new StringBuilder();
		sb.append("select ");
		sb.append("		f.id,");
		sb.append("		f.name,");
		sb.append("		f.price,");
		sb.append("		f.vip_price,");
		sb.append("		f.foodType_id,");
		sb.append("		f.description,");
		sb.append("		f.img ");
		sb.append("from ");
		sb.append("		food f ");
		sb.append("where 1=1 ");
		
		Condition condition=pageBean.getCondition();
		int foodTypeId=condition.getFoodTypeId();
		String foodName=condition.getFoodName();
		List<Object> conditionList=new ArrayList<Object>();
		if(foodTypeId>0){
			sb.append(" and f.foodType_id=?");
			conditionList.add(foodTypeId);
		}
		if(foodName!=null){
			sb.append(" and f.name like ?");
			conditionList.add("%"+foodName+"%");
		}
		//排序
		sb.append("	order by f.id asc");
		//执行分页查询
		if(condition.isPage()){
			sb.append("	limit ?,?");
			int start=(pageBean.getCurrentPage()-1)*pageBean.getRows();
			conditionList.add(start);
			conditionList.add(pageBean.getRows());
		}
		
		try{
			return JdbcUtils.getQueryRunner().query(
					sb.toString(),
					new BeanListHandler<Food>(Food.class),
					conditionList.toArray());
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public int getFoodCount(PageBean pageBean) {
		StringBuilder sb=new StringBuilder();
		sb.append("select ");
		sb.append("		count(f.id) ");
		sb.append("from ");
		sb.append("		food f ");
		sb.append("where 1=1 ");
		
		Condition condition=pageBean.getCondition();
		int foodTypeId=condition.getFoodTypeId();
		String foodName=condition.getFoodName();
		List<Object> conditionList=new ArrayList<Object>();
		if(foodTypeId>0){
			sb.append(" and f.foodType_id=?");
			conditionList.add(foodTypeId);
		}
		if(foodName!=null){
			sb.append(" and f.name like ?");
			conditionList.add("%"+foodName+"%");
		}
		try{
			Long count=JdbcUtils.getQueryRunner().query(
					sb.toString(),
					new ScalarHandler<Long>(),
					conditionList.toArray());
			return count.intValue();
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public List<Object[]> getAll(PageBean pageBean) {
		StringBuilder sb=new StringBuilder();
		sb.append("select ");
		sb.append("		f.id,");
		sb.append("		f.name,");
		sb.append("		f.price,");
		sb.append("		f.vip_price,");
		sb.append("		f.foodType_id,");
		sb.append("		ft.name,");
		sb.append("		f.description,");
		sb.append("		f.img ");
		sb.append("from ");
		sb.append("		food f,");
		sb.append("		foodType ft ");
		sb.append("where 1=1 ");
		sb.append("		and f.foodType_id=ft.id ");
		//把查询条件拼接到sql字符串中
		Condition condition=pageBean.getCondition();
		//使用集合去封装查询条件，在调用query方法时，转化为数组
		List<Object> list=new ArrayList<Object>();
		int foodTypeId=condition.getFoodTypeId();
		if(foodTypeId>0){
			sb.append("	and f.foodType_id=?");
			list.add(foodTypeId);
		}
		String foodName=condition.getFoodName();
		if(foodName!=null){
			sb.append("	and f.name like ?");
			list.add("%"+foodName+"%");
		}
		//排序
		sb.append("	order by f.id asc");
		//执行分页查询
		if(condition.isPage()){
			sb.append("	limit ?,?");
			int start=(pageBean.getCurrentPage()-1)*pageBean.getRows();
			list.add(start);
			list.add(pageBean.getRows());
		}
		//执行sql语句
		try{
			return JdbcUtils.getQueryRunner().query(
					sb.toString(),
					new ArrayListHandler(),
					list.toArray());
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	@Override
	public int getCount(PageBean pageBean) {
		StringBuilder sb=new StringBuilder();
		sb.append("select ");
		sb.append("		count(f.id) ");
		sb.append("from ");
		sb.append("		food f,");
		sb.append("		foodType ft ");
		sb.append("where 1=1 ");
		sb.append("		and f.foodType_id=ft.id ");
		
		Condition condition=pageBean.getCondition();
		int foodTypeId=condition.getFoodTypeId();
		String foodName=condition.getFoodName();
		List<Object> conditionList=new ArrayList<Object>();
		if(foodTypeId>0){
			sb.append(" and ft.id=?");
			conditionList.add(foodTypeId);
		}
		if(foodName!=null){
			sb.append(" and f.name like ?");
			conditionList.add("%"+foodName+"%");
		}
		try{
			Long count=JdbcUtils.getQueryRunner().query(
					sb.toString(),
					new ScalarHandler<Long>(),
					conditionList.toArray());
			return count.intValue();
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	@Override
	public int getFoodCount() {
		String sql="select count(id) from food";
		try {
			Long count=JdbcUtils.getQueryRunner().query(sql, new ScalarHandler<Long>());
			return count.intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	@Override
	public Food getFoodById(int id) {
		String sql="select * from food where id=?";
		try{
			return JdbcUtils.getQueryRunner().query(sql,new BeanHandler<Food>(Food.class),id);
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Food> select(String str) {
		String sql=null;
		Object condition=null;
		if(str.matches("[0-9]+")){
			//输入的是数字，按照id查询
			condition=Integer.parseInt(str);
			sql="select * from food where id=?";
		}
		//匹配所有的中文，英文字符一次或多次，或者匹配所有的空格、制表符、换行符等空白字符0次或多次
		if(str.matches("[\u4e00-\u9fa5a-zA-Z]+|\\s*")){
			//输入的是中文，按照名称查询
			condition=str.toString();
			condition="%"+condition+"%";
			sql="select * from food where name like ?";
		}
		try{
			return JdbcUtils.getQueryRunner().query(sql,new BeanListHandler<Food>(Food.class),condition);
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Food> getAll() {
		String sql="select * from food ";
		try {
			return JdbcUtils.getQueryRunner().query(sql,new BeanListHandler<Food>(Food.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Food> getByFoodType(int foodTypeId) {
		String sql="select * from food where foodType_id=? ";
		try {
			return JdbcUtils.getQueryRunner().query(sql,new BeanListHandler<Food>(Food.class),foodTypeId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	
}
